package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StaffDao {
    public StaffDao() {
    }

    public boolean creaStaff(String FirstName, String LastName,
                             Date birth, String sex, int SSN, int position,
                             float salary, String nickname,
                             String clave) {
        int numberS=-5;
        Conexion conexion = new Conexion();
        boolean creado = false;
        Connection conex;

        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;
            ResultSet rs = null;

            ResultSet rsa = null;
            int mayor = -1;
            ps = conex.prepareStatement("select max (staffNumber)  from staff");

            rsa = ps.executeQuery();
            if (rsa != null) {
                while (rsa.next()) {
                    mayor = rsa.getInt(1);
                }
            } else
                numberS = 1;
                numberS = mayor + 1;
            
                ps =
                conex.prepareStatement("INSERT INTO STAFF (StaffNumber, FirstName,LastName,DateOfBirth,Sex,SSN,Position,Salary,Nickname,Clave) VALUES (?,?,?,?,?,?,?,?,?,?)");
            
            ps.setInt(1, numberS);               
            ps.setString(2, FirstName);
            ps.setString(3, LastName);
            ps.setDate(4, birth);
            ps.setString(5, sex);
            ps.setInt(6, SSN);
            ps.setInt(7, position);
            ps.setFloat(8, salary);
            ps.setString(9, nickname);
            ps.setString(10, clave);
           
            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que inserto fueron" +
                               numeroFilas);
            creado = true;
            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());

        }
        return creado;
    }

       public boolean eliminarStaff(int numberS) {

        Conexion conexion = new Conexion();
        boolean eliminado = false;
        Connection conex;

        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;
            ResultSet rs = null;

            ps = conex.prepareStatement("DELETE FROM Staff where StaffNumber = ?");

            ps.setInt(1, numberS);


            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que borro fueron" +
                               numeroFilas);
            eliminado = true;
            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());

        }
        return eliminado;
    }
    
    public boolean modificaStaffFirstName(int numberS, String Fname) {
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;

            ps =
    conex.prepareStatement("UPDATE Staff set FirstName = ?  where StaffNumber = ?");

            ps.setString(1, Fname);
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
    
    public boolean modificaStaffLastName(int numberS, String Lname) {
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;

            ps =
    conex.prepareStatement("UPDATE Staff set LastName = ?  where StaffNumber = ?");

            ps.setString(1, Lname);
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
    
       public boolean modificaStaffBirth(int numberS, Date cumple) {
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;

            ps =
    conex.prepareStatement("UPDATE Staff set DateOfBirth = ?  where StaffNumber = ?");

            ps.setDate(1, cumple);
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }

    public boolean modificaStaffSex(int numberS) {
        String cambio="";
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;
            ResultSet rs=null;
            
            ps =
            conex.prepareStatement("Select Sex from Staff where StaffNumber = ?");
            ps.setInt(1,numberS);
            rs=ps.executeQuery();
            if (rs.next())
            {  cambio=rs.getString(1);
              }
            else {conex.close(); return false;}
            
            ps =
            conex.prepareStatement("UPDATE Staff set Sex = ?  where StaffNumber = ?");

             if (cambio.equalsIgnoreCase("M"))
            ps.setString(1, "F");
            else
            ps.setString(1, "M");
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
    
    public boolean modificaSSN(int numberS, int seguro) {
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;

            ps =
            conex.prepareStatement("UPDATE Staff set SSN = ?  where StaffNumber = ?");

            ps.setInt(1, seguro);
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
    
    public boolean modificaStaffPosition(int numberS) {
        String cambio="";
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;
            ResultSet rs=null;
            ps =
            conex.prepareStatement("Select Position from Staff  where StaffNumber = ?");
            ps.setInt(1,numberS);
            rs=ps.executeQuery();
            
            if (rs.next())
            {   cambio=rs.getString(1);
                }
            else
            {conex.close();return false;}
            
            ps =
    conex.prepareStatement("UPDATE Staff set Position = ?  where StaffNumber = ?");
 
            if (!cambio.equalsIgnoreCase("1"))
            ps.setString(1, "1");
            else
            ps.setString(1, "2");    
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
    
    public boolean modificaStaffSalary(int numberS, float sueldo) {
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;

            ps =
    conex.prepareStatement("UPDATE Staff set salary = ?  where StaffNumber = ?");

            ps.setFloat(1, sueldo);
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
    
    public boolean modificaStaffNickName(int numberS, String nn) {
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;

            ps =
    conex.prepareStatement("UPDATE Staff set nickname = ?  where StaffNumber = ?");

            ps.setString(1, nn);
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
    
    public boolean modificaStaffClave(int numberS, String c) {
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;

            ps =
    conex.prepareStatement("UPDATE Staff set clave = ?  where StaffNumber = ?");

            ps.setString(1, c);
            ps.setInt(2, numberS);

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);

            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
    
    
}
